package com.apexedu.portal.service.impl;


import com.apexedu.framework.dao.BaseDao;
import com.apexedu.framework.util.DBUtil;
import com.apexedu.framework.util.Pager;
import com.apexedu.identity.IdConstants;
import com.apexedu.identity.entity.TSysUser;
import com.apexedu.portal.entity.TDb;
import com.apexedu.portal.entity.TFrontdeskNews;
import com.apexedu.portal.entity.TWz;
import com.apexedu.portal.entity.TWzAttachment;
import com.apexedu.portal.util.DBMUtil;
import com.apexedu.sys.entity.TSysReportfactory;
import com.apexedu.util.StringUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

/**
 * 上海美院门户
 *
 * @datetime 2015.07.22
 */
@Service("mhBaseService")
public class MhBaseServiceImpl {

    private static final Log LOG = LogFactory.getLog(MhBaseServiceImpl.class);
    @Autowired
    BaseDao baseDao;

    /**
     * 门户首页查询该位置新闻列表
     *
     * @param location
     * @param rownum
     * @return
     */
    public List<TWz> queryNewsEntity(HttpServletRequest request,String location, String rownum) {
        List<TWz> al_new = new ArrayList<TWz>();
        TSysReportfactory reportfactory =new TSysReportfactory();
        TDb tdb =new TDb();
        Pager pager=new Pager();
        //查询该用户该位置有无添加报表
        String sql_fdn=" select t.wid,t.lmmc,(select code from t_db where wid=t.code) as code,t.querysql,t.weiz,t.shuom,t.state " +
                " from T_FRONTDESK_NEWS t"+
                " where 1=1 and t.state=1 and t.weiz=? ";
        List<TFrontdeskNews> tfrontdesknewslist = DBUtil.queryAllBeanList(sql_fdn, TFrontdeskNews.class, location);
        if (tfrontdesknewslist!=null&& tfrontdesknewslist.size()>0){
            TFrontdeskNews tfrontdesknews =tfrontdesknewslist.get(0);
            request.setAttribute("tfrontdesknews", tfrontdesknews);
            String sql_new = tfrontdesknews.getQuerysql();
            
            DBMUtil dbmUtil = new DBMUtil(tfrontdesknews.getCode());
            tdb =DBUtil.queryBean("select * from t_db where code=? and rownum<2" ,TDb.class,tfrontdesknews.getCode());
            pager.setEachPageRows(Integer.parseInt(rownum));
            try {
            	//替换user
                TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
        		String userId = user.getUserloginid();
        		sql_new = sql_new.replaceAll("\\{userid\\}", userId);
            	
                if (tdb.getUrl().indexOf("sqlserver")!=-1){
                    long c = dbmUtil.count("select count(*) from ("+sql_new+") as t ");
                    pager.setTotalRows(c);
                    String sql="select top "+pager.getEachPageRows()+" * from (" + sql_new + ") as t where 1=1   wid not in (select top "+(pager.getCurrentPageno() - 1) * pager.getEachPageRows()+" wid from (" + sql_new + ") as s where 1=1 ) " ;
                    al_new = dbmUtil.queryAllBeanList(sql,TWz.class);
                }else if(tdb.getUrl().indexOf("oracle")!=-1){
                	String sql_temp = "select count(*) from ("+sql_new+")";
                    long c = dbmUtil.count(sql_temp);
                    pager.setTotalRows(c);
                    String sql = "select * from (" + sql_new + ") where 1=1 ";
                    al_new = dbmUtil.queryPageBeanList(sql , pager, TWz.class);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return al_new;
    }

    /**
     * 根据关键字分页查询新闻列表
     * @param request
     * @param location
     * @param pager
     * @param gjz
     * @return
     */
    public List<TWz> queryNewsPagesList(HttpServletRequest request,String location, Pager pager,String gjz) {
        List<TWz> tWzList = new ArrayList<TWz>();
        TDb tdb =new TDb();
        String sql_fdn = " select t.wid,t.lmmc,(select code from t_db where wid=t.code) as code,t.querysql,t.weiz,t.shuom,t.state " +
                " from T_FRONTDESK_NEWS t" +
                " where 1=1 and t.state=1 and t.weiz=? ";
        String conditions="";
        List<TFrontdeskNews> tfrontdesknewslist = DBUtil.queryAllBeanList(sql_fdn, TFrontdeskNews.class, location);
        if (tfrontdesknewslist != null && tfrontdesknewslist.size() > 0) {
            TFrontdeskNews tfrontdesknews = tfrontdesknewslist.get(0);
            request.setAttribute("tfrontdesknews", tfrontdesknews);
            String sql_new = tfrontdesknews.getQuerysql();
            if (StringUtil.isNotBlank(gjz)){
                request.setAttribute("gjz", gjz);
                try {
                    gjz=java.net.URLDecoder.decode(gjz, "UTF-8");
                    request.setAttribute("xs", gjz);
                    conditions=" and (title like '%"+gjz+"%' )";
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }
            DBMUtil dbmUtil = new DBMUtil(tfrontdesknews.getCode());
            tdb =DBUtil.queryBean("select * from t_db where code=? and rownum<2" ,TDb.class,tfrontdesknews.getCode());
            pager.setEachPageRows(9);
            try {
            	
            	//替换user
                TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
        		String userId = user.getUserloginid();
        		sql_new = sql_new.replaceAll("\\{userid\\}", userId);
            	
                if (tdb.getUrl().indexOf("sqlserver")!=-1){
                    long c = dbmUtil.count("select count(*) from ("+sql_new+") as t where 1=1 "+conditions);
                    pager.setTotalRows(c);
                    String sql="select top "+pager.getEachPageRows()+" * from (" + sql_new + ") as t where 1=1  "+conditions+" and  wid not in (select top "+(pager.getCurrentPageno() - 1) * pager.getEachPageRows()+" wid from (" + sql_new + ") as s where 1=1 "+conditions+" ) " ;
                    tWzList = dbmUtil.queryAllBeanList(sql,TWz.class);
                }else if(tdb.getUrl().indexOf("oracle")!=-1){
                    long c = dbmUtil.count("select count(*) from ("+sql_new+") where 1=1 "+conditions);
                    pager.setTotalRows(c);
                    tWzList = dbmUtil.queryPageBeanList("select * from (" + sql_new + ") where 1=1 " + conditions, pager, TWz.class);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  tWzList;
    }

    /**
     * 根据wid浏览新闻
     * @param request
     * @param location
     * @param wid
     * @return
     */
    public TWz queryNewsBean(HttpServletRequest request,String location, String wid) {
        TWz twz = new TWz();
        TDb tdb = new TDb();
        List<TWzAttachment> tWzAttachmentList = new ArrayList<TWzAttachment>();
        String sql_fdn = " select t.wid,t.lmmc,(select code from t_db where wid=t.code) as code,t.querysql,t.weiz,t.shuom,t.state,t.updatesql" +
                " from T_FRONTDESK_NEWS t" +
                " where 1=1 and t.state=1 and t.weiz=? ";
        List<TFrontdeskNews> tfrontdesknewslist = DBUtil.queryAllBeanList(sql_fdn, TFrontdeskNews.class, location);
        if (tfrontdesknewslist != null && tfrontdesknewslist.size() > 0) {
            TFrontdeskNews tfrontdesknews = tfrontdesknewslist.get(0);
            request.setAttribute("tfrontdesknews", tfrontdesknews);
            String sql_new = tfrontdesknews.getQuerysql();
            String update_sql = tfrontdesknews.getUpdatesql();
            DBMUtil dbmUtil = new DBMUtil(tfrontdesknews.getCode());
            tdb = DBUtil.queryBean("select * from t_db where code=? and rownum<2", TDb.class, tfrontdesknews.getCode());
            if (StringUtil.isNotBlank(update_sql)) {
                try {
                    int i = dbmUtil.executeSQL(update_sql, wid);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (tdb.getUrl().indexOf("sqlserver") != -1) {
                sql_new = " select * from (" + sql_new + " )  as t where wid=?";
            } else if (tdb.getUrl().indexOf("oracle") != -1) {
                sql_new = " select * from (" + sql_new + " ) where wid=?";
            }
            
            try {
            	//替换user
                TSysUser user = (TSysUser) request.getSession().getAttribute(IdConstants.SESSION_USER);
        		String userId = user.getUserloginid();
        		sql_new = sql_new.replaceAll("\\{userid\\}", userId);
            	
                twz = dbmUtil.queryBean(sql_new, TWz.class, wid);
                tWzAttachmentList = dbmUtil.queryAllBeanList("select id,title,document,memo,tblog_id as tblogId from lbNewsAttachment where tblog_id=?", TWzAttachment.class, wid);
                request.setAttribute("twzAttachmentList", tWzAttachmentList);
            } catch (Exception e) {
                System.out.println("E=" + e.toString());
                e.printStackTrace();
            }
        }
        return twz;
    }
}
